<?php
/*
    [GRIRMS] (C) 2010 Grandison & JM Technologies

    @author kevin<hasson.lai@mobile-grandison.com>
    @version $Id: datatable.class.php v2 2010-09-20 $
*/

/*
	todo:
	add_fields('a.*,b.name')
	add_table('aa a')
	add_table('bb b on a.xx=b.xx', 'left join')
	
	print dbug

*/

if(!defined('IN_GRIRMS')) {
	exit('Access Denied');
}

define('PHPEXCEL_PATH', APP_ROOT.'./include/');

class zz_datatable{
	private $data = array();
	private $caption = 'datatable title';
	private $url = '';
	private $sql = '';
	private $page = 0;
	private $pagesize = 0;
	private $records = -1;
	private $pager = '#pager';
	private $columns = array();
	private $property = array(
		'width' => null,
		'sort' => null,
		'align' => null,
		'valign' => null,
		'key' => null,
		'tpl' => null,
		'format' => null,
		'format' => null,
		'call' => null
	);
	private $db = null;
	private $where = array();
	private $order = '';
	private $groupby = '';
	private $postback = null;
	private $export = null;
	
	public function __construct($page = 1, $records = -1, $data = array()){
		global $db;
		
		$this->postback = isset($_REQUEST['tblStatus']);
		$this->export = isset($_REQUEST['tblExport']);
		
		$this->url = $_SERVER["REQUEST_URI"];
		$this->db = $db;
		
		$this->set_page($page);
		$this->set_records($records);
		$this->set_data($data);
		
	}
	
	public function export(){
		return $this->export;
	}
	
	public function postback(){
		return $this->postback;
	}
	
	public function add_column($name, $title = null, $call = null, $property = array(), $export = false){
		$title = $title == null ? $name : $title;
		if(array_key_exists('tpl', $property)){
			$property['tpl'] = str_replace('\'', '\\\'', $property['tpl']);
		}
		$column = array(
			'name' => $name,
			'title' => $title,
			'call' => $call,
			'export' => $export,
			'property' => array_merge($this->property, $property)
		);
		$this->columns[] = $column;
		return $this;
	}
	
	public function add_condition($condition){
		$this->where[] = $condition;
		return $this;
	}
	
	public function process_count($total_nums){
		$page_size = intval($_REQUEST['rows']) > 0 ? $_REQUEST['rows'] : 20;
		$total_pages = ceil($total_nums / $page_size);
		$total_pages <= 0 && $total_pages = 1;
		$page = intval($_REQUEST['page']) < 1 ? 1 : intval($_REQUEST['page']);
		$page > $total_pages && $page = $total_pages;
		$start_nums = $page_size * ($page - 1);
		if($start_nums < 0) $start_nums = 0;
		$this->set_page($page);
		$this->set_records($total_nums);
		//$this->pagesize($page_size);
		return array($start_nums, $page_size);
	}
	
	public function get_count_sql($sql){
		return preg_replace('/^(select)(.*?)(from)/is', '\1 COUNT(*) \3', $sql);
	}
	
	public function set_data($data){
		$this->data = $data;
		if($this->records == -1)  $this->records = count($data);
		return $this;
	}
	
	public function process_data(){
		$tblStatus = unserialize(authcode(str_replace(' ','+',$_REQUEST['tblStatus'])));
		//组织$sql
		$sql = $this->sql;
		//条件
		if(!empty($this->where)){
			$sql .= ' WHERE '.implode(' AND ', $this->where);
		}
		//组排序
		if(!empty($this->groupby)){
			$sql .= ' GROUP BY '.$this->groupby;
		}
		//获取总数
		$total_nums = $this->db->result_first($this->get_count_sql($sql));
		//排序
		if(isset($_REQUEST['sort']) && trim($_REQUEST['sort']) != ''){
			$sort = trim($_REQUEST['sort']);
			$sql .= ' ORDER BY '.$sort;
		}else if(!empty($this->order)){
			$sql .= ' ORDER BY '.$this->order;
		}
		//获取指定条数
		if(!$this->export){
			list($start_nums, $page_size) = $this->process_count($total_nums);
			$sql .= " LIMIT $start_nums,$page_size";
		}
		$this->set_data($this->db->fetch_all($sql));
		$data = array();

		foreach($this->data as $row){
			if(count($tblStatus) > 0){
				$_row = array();
				foreach($tblStatus as $k => $v){
					if(($this->export && $k == 'op') || (!$this->export && $v['export'])) continue;
					$call = $v['call'];
					$_row[$k] = ($call != null && function_exists($call)) ? $call($row[$k], $row) : $row[$k];
				}
				$data[] = $_row;
			}
		}
		return $data;
	}

	public function show(&$out = null){
		//判断是组织表格还是显示数据或者导出数据
		if($this->export){
			//导出数据
			$this->export_excel();
		}else if($this->postback){
			$data = $this->process_data();
			$out_arr = array('page' => $this->page, 'records' => $this->records, 'data' => $data);
			header("Expires:Sat, 1 Jan 2000 08:00:00 GMT ");
			header("Last-Modified: ".gmdate("D, d M Y H:i:s")." GMT ");
			header("Cache-Control: no-cache, must-revalidate ");
			header("Pragma: no-cache ");
			$out_js = array2json($out_arr, true);
			exit($out_js);
		}else{
			$caption = $this->caption;
			$operation = str_replace("'", "\\'", $this->operation);
			$url = $this->url;
			$titles = "[";
			$columns = "[";
			$tblStatus = array();
			$column_comma = '';
			foreach($this->columns as $column){
				if(!$column['export']){
					$titles .= "$column_comma'{$column['title']}'";
					$columns .= "$column_comma\n		             {";
					$columns .= "name:'{$column['name']}'";
					foreach($column['property'] as $k => $v){
						if($v != null) $columns .= ", $k:'$v'";
					}
					$columns .= "}";
					$column_comma = ',';
				}
				$tblStatus[$column['name']] = $column;
			}
			$titles .= "]";
			$columns .= "\n		          ]";
			//$columns = array2json($this->columns);
			$tblStatus = authcode(serialize($tblStatus), 'ENCODE');
			//$tblStatus = serialize($tblStatus);
			$out_js =  "
<script type=\"text/javascript\">
$(document).ready(function(){
    $('#datagrid').dataTable({
        url: '$url',
		colNames : $titles,
		columns: $columns,
		rownum : '$this->pagesize',
		page : '$this->page',
		pager : '$this->pager',
		caption : '$caption',
		tblStatus : '$tblStatus'
	});
});
</script>";
			if(isset($out)){
				$out = $out_js;
			}
			return $out_js;
		}
	}
	
	function export_excel(){
		require_once PHPEXCEL_PATH.'PHPExcel.php';  
		require_once PHPEXCEL_PATH.'PHPExcel/Writer/Excel5.php';

		// 创建一个处理对象实例  
		$objExcel = new PHPExcel();  
		$objWriter = new PHPExcel_Writer_Excel5($objExcel); 
		  
		//设置文档基本属性
		$objProps = $objExcel->getProperties();  
		$objProps->setCreator("GRIRMS");  
		$objProps->setLastModifiedBy("GRIRMS");  
		$objProps->setTitle("GRIRMS XLS Document");  
		$objProps->setSubject("GRIRMS XLS Document, GRIRMS");  
		$objProps->setDescription("GRIRMS document, generated by GRIRMS.");  
		$objProps->setKeywords("GRIRMS,Kevin");  
		$objProps->setCategory("GRIRMS"); 
		  
		//设置当前的sheet索引
		$objExcel->setActiveSheetIndex(0);  
		$objActSheet = $objExcel->getActiveSheet();  
		  
		//设置当前活动sheet的名称  
		$objActSheet->setTitle('Sheet1');  
		  

		//设置表头
		$tblStatus = unserialize(authcode(str_replace(' ','+',$_REQUEST['tblStatus'])));
		$c = 0;
		foreach($tblStatus as $k => $v){
			$c++;
			if($k == 'op') continue;
			$objActSheet->setCellValue($this->get_letter($c).'1', $v['title']);
		}
		
		//设置对齐方式  
		//$objAlignA5 = $objStyleA5->getAlignment();  
		//$objAlignA5->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);  
		//$objAlignA5->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); 
		
		//填充内容
		error_reporting(0);
		$data = $this->process_data();
		$r = 1;
		foreach($data as $row){
			$r++;
			$c = 0;
			$mh = 0; //当前列最高
			foreach($tblStatus as $k => $v){
				$c++;
				$ch = 0; //当前列高
				if($v['property']['format'] == 'image'){
					if(file_exists($row[$k])){
						$image = $row[$k];
						list($owidth, $oheight, $type) = getimagesize($image);
						//图片尺寸缩小为三倍
						$width = intval($owidth / 3);
						$height = intval($oheight / 3);
						if(intval($v['property']['width']) < intval($width)) $tblStatus[$k]['property']['width'] = $width;
						if($ch == 0) $ch = $height;
						$im = null;
						switch($type){
							case 1:
								$im = @imagecreatefromgif($image);
								break;
							case 2:
								$im = @imagecreatefromjpeg($image);
								break;
							case 3:
								$im = @imagecreatefrompng($image);
								break;
							default:
								$im = @imagecreatefromstring($image);
								break;
						}
						
						/* $newim = null;
						if(function_exists("imagecopyresampled")){
							$newim = imagecreatetruecolor($width,$height);
							imagecopyresampled($im,$im,0,0,0,0,$width,$height,$owidth,$oheight);
						}else{
							$newim = imagecreate($width,$height);
							imagecopyresized($im,$im,0,0,0,0,$width,$height,$owidth,$oheight);
						}
						imagedestroy($im); */

						$objDrawing = new PHPExcel_Worksheet_MemoryDrawing();
						$objDrawing->setName($k);
						$objDrawing->setDescription($v['title']);
						$objDrawing->setImageResource($im);
						$objDrawing->setWidth($width);
						$objDrawing->setHeight($height);
						if($type == 1) $objDrawing->setRenderingFunction(PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_GIF);
						else if($type == 2) $objDrawing->setRenderingFunction(PHPExcel_Worksheet_MemoryDrawing::RENDERING_JPEG);
						else if($type == 3) $objDrawing->setRenderingFunction(PHPExcel_Worksheet_MemoryDrawing::RENDERING_PNG);
						$objDrawing->setMimeType(PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_DEFAULT);
						//$objDrawing->setPath($image);
						//if($v['property']['width']) $objDrawing->setWidth($v['property']['width']);  
						//if($v['property']['height']) $objDrawing->setHeight($v['property']['height']);
						$objDrawing->setCoordinates($this->get_letter($c).$r);
						//$objDrawing->setOffsetX(0);
						//$objDrawing->setRotation(0);
						//$objDrawing->getShadow()->setVisible(false);
						//$objDrawing->getShadow()->setDirection(10);
						$objDrawing->setWorksheet($objActSheet);

					}else{
						$objActSheet->setCellValue($this->get_letter($c).$r, '');
					}
					if($ch > $mh) $mh = $ch;
				}else if($v['property']['format'] == 'string'){
					$objActSheet->setCellValue($this->get_letter($c).$r, '\''.$row[$k]);
				}else{
					$objActSheet->setCellValue($this->get_letter($c).$r, $row[$k]);
				}
			}
			if($mh >0) $objActSheet->getRowDimension($r)->setRowHeight(intval($mh / 1.2));
		}
		
		//设置列宽
		$c = 0;
		foreach($tblStatus as $k => $v){
			$c++;
			if($k == 'op') continue;
			if($v['property']['width']) $objActSheet->getColumnDimension($this->get_letter($c))->setWidth(intval($v['property']['width']/6.5));
			else $objActSheet->getColumnDimension($this->get_letter($c))->setAutoSize(true);
		}
		  

		//输出内容到浏览器 
		$outputFileName = date('YmdHis').rand(1000,9999).'.xls';
		header('Content-type: application/vnd.ms-excel'); 
		header('Content-Disposition:inline;filename="'.$outputFileName.'"');  
		header("Content-Transfer-Encoding: binary");  
		header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");  
		header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");  
		header("Cache-Control: must-revalidate, post-check=0, pre-check=0");  
		header("Pragma: no-cache");
		$objWriter->save('php://output');
		exit();
	}
	
	private function dbug($msg,$line){
		$self = basename(__FILE__);
		exit("<p>file:[{$self}],line:[{$line}],msg:[{$msg}]</p>");
	}

	public function __toString(){
		return $this->show();
	}
	
	public function __call($func, $params){
		
		if(preg_match('/^set_(\w+)$/', $func, $match) && !empty($match[1]) && count($params) >= 1){
			switch($match[1]){
				case 'caption':
					$this->caption = $params[0];
					break;
				case 'url':
					$this->url = $params[0];
					break;
				case 'sql':
					$this->sql = $params[0];
					break;
				case 'pagesize':
					$this->pagesize = $params[0];
					break;
				case 'page':
					$this->page = intval($params[0]);
					break;
				case 'records':
					$this->records = intval($params[0]);
					break;
				case 'pager':
					$this->pager = $params[0];
					break;
				case 'order':
					$this->order = $params[0];
					break;
				case 'groupby':
					$this->groupby = $params[0];
					break;
				default:
				
					break;
			}
		}
		return $this;
	}
	
	//十进制换成字母进制
	private function get_letter($num){
		$ret = array();
		do{
			$n = $num % 26;
			if($n == 0) $n = 26;
			array_push($ret, $n);
			$num = $num / 26;
		}while($num > 1);
		$len = count($ret);
		$letter = '';
		for($i = $len - 1; $i >=0; $i--){
			$letter .= chr(64 + $ret[$i]);
		}
		return $letter;
	}

}
?>
